﻿<cfsilent>
	<cfscript>
		
		temFile = GetTempDirectory() & createUUID() & ".xls";

		termId = event.getArg("TermID");

		academicYear = event.getArg("AcademicYear");
		academicTerm = event.getArg("AcademicTerm");
		
		downFile = URLEncodedFormat("教材征订计划." & academicYear & "-" & academicTerm, "utf-8");
		
		/* 生成电子表格数据 */
		sql = "	SELECT
					cb.cbid,i.institute_name,
					c.course_name,cb.bidx,
					b.book_name,b.book_authors,b.book_edition,p.publisher,b.book_prise,b.book_isbn,
					cb.plan_student,cb.plan_teacher,
					CASE WHEN cb.contract IS NULL THEN '-' ELSE cb.contract END contract,
					CASE WHEN cb.telephone IS NULL THEN '-' ELSE cb.telephone END telephone
				FROM
					t_course_book cb INNER JOIN t_course c ON c.cid = cb.cid INNER JOIN t_institute i ON i.institute_id = c.institute_id INNER JOIN t_book b ON b.bid = cb.bid
					INNER JOIN t_publisher p ON p.pid = b.pid
				WHERE
					cb.term_id = :termId 
				ORDER BY
					i.institute_name,c.course_name,cb.bidx";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "termId", value = termId, cfsqltype = "cf_sql_char");
		rs_plan = queryObj.execute(sql = sql).getResult();
		
		/* 载入相关操作组件 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("学期征订计划", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "征订计划号,任课单位,课程,书序号,书名,作者,版次,出版社,定价,ISBN,ISBN10,学生用书,教师用书,联系人,联系电话");
		
		row = 1;
		
		/* 写入计划任务 */
		for( a = 1; a le rs_plan.recordCount; a++ ){
			
			row++;
			
			rowData = replace(rs_plan["cbid"][a], ",", "-", "ALL")
						& "," & rs_plan["institute_name"][a]
						& "," & rs_plan["course_name"][a]
						& "," & rs_plan["bidx"][a]
						& "," & replace( rs_plan["book_name"][a], ",", "", "ALL")
						& "," & replace( rs_plan["book_authors"][a], "/", " | ", "ALL")
						& "," & dictAdvice.getBookEdition( rs_plan["book_edition"][a], "")
						& "," & rs_plan["publisher"][a]
						& "," & rs_plan["book_prise"][a];
			
			if ( len(rs_plan["book_isbn"][a]) eq 10 ) {
				
				rowData = rowData 
							& "," & replace( dictAdvice.getISBN( mathAdvice.upgradeISBN( rs_plan["book_isbn"][a] ) ), "ISBN ", "")
							& "," & replace( dictAdvice.getISBN( rs_plan["book_isbn"][a] ), "ISBN ", "");
			
			}
			else {
				
				rowData = rowData 
							& "," & replace( dictAdvice.getISBN( rs_plan["book_isbn"][a] ), "ISBN ", "")
							& "," & " ";
			}
					
			rowData = rowData 
							& "," & rs_plan["plan_student"][a]
							& "," & rs_plan["plan_teacher"][a]
							& "," & rs_plan["contract"][a]
							& "," & rs_plan["telephone"][a];
							
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
	
	</cfscript>
	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />
</cfsilent>